'\" t
.\"     Title: SPI_execute
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets v1.75.2 <http://docbook.sf.net/>
.\"      Date: 2011-12-01
.\"    Manual: PostgreSQL 9.1.2 Documentation
.\"    Source: PostgreSQL 9.1.2
.\"  Language: English
.\"
.TH "SPI_EXECUTE" "3" "2011-12-01" "PostgreSQL 9.1.2" "PostgreSQL 9.1.2 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
SPI_execute \- execute a command
.\" SPI_execute
.SH "SYNOPSIS"
.sp
.nf
int SPI_execute(const char * \fIcommand\fR, bool \fIread_only\fR, long \fIcount\fR)
.fi
.SH "DESCRIPTION"
.PP

\fBSPI_execute\fR
executes the specified SQL command for
\fIcount\fR
rows\&. If
\fIread_only\fR
is
true, the command must be read\-only, and execution overhead is somewhat reduced\&.
.PP
This function can only be called from a connected procedure\&.
.PP
If
\fIcount\fR
is zero then the command is executed for all rows that it applies to\&. If
\fIcount\fR
is greater than 0, then the number of rows for which the command will be executed is restricted (much like a
LIMIT
clause)\&. For example:
.sp
.if n \{\
.RS 4
.\}
.nf
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
.fi
.if n \{\
.RE
.\}
.sp
will allow at most 5 rows to be inserted into the table\&.
.PP
You can pass multiple commands in one string, but later commands cannot depend on the creation of objects earlier in the string, because the whole string will be parsed and planned before execution begins\&.
\fBSPI_execute\fR
returns the result for the command executed last\&. The
\fIcount\fR
limit applies to each command separately, but it is not applied to hidden commands generated by rules\&.
.PP
When
\fIread_only\fR
is
false,
\fBSPI_execute\fR
increments the command counter and computes a new
snapshot
before executing each command in the string\&. The snapshot does not actually change if the current transaction isolation level is
SERIALIZABLE
or
REPEATABLE READ, but in
READ COMMITTED
mode the snapshot update allows each command to see the results of newly committed transactions from other sessions\&. This is essential for consistent behavior when the commands are modifying the database\&.
.PP
When
\fIread_only\fR
is
true,
\fBSPI_execute\fR
does not update either the snapshot or the command counter, and it allows only plain
SELECT
commands to appear in the command string\&. The commands are executed using the snapshot previously established for the surrounding query\&. This execution mode is somewhat faster than the read/write mode due to eliminating per\-command overhead\&. It also allows genuinely
stable
functions to be built: since successive executions will all use the same snapshot, there will be no change in the results\&.
.PP
It is generally unwise to mix read\-only and read\-write commands within a single function using SPI; that could result in very confusing behavior, since the read\-only queries would not see the results of any database updates done by the read\-write queries\&.
.PP
The actual number of rows for which the (last) command was executed is returned in the global variable
\fISPI_processed\fR\&. If the return value of the function is
SPI_OK_SELECT,
SPI_OK_INSERT_RETURNING,
SPI_OK_DELETE_RETURNING, or
SPI_OK_UPDATE_RETURNING, then you can use the global pointer
SPITupleTable *SPI_tuptable
to access the result rows\&. Some utility commands (such as
EXPLAIN) also return row sets, and
SPI_tuptable
will contain the result in these cases too\&.
.PP
The structure
SPITupleTable
is defined thus:
.sp
.if n \{\
.RS 4
.\}
.nf
typedef struct
{
    MemoryContext tuptabcxt;    /* memory context of result table */
    uint32      alloced;        /* number of alloced vals */
    uint32      free;           /* number of free vals */
    TupleDesc   tupdesc;        /* row descriptor */
    HeapTuple  *vals;           /* rows */
} SPITupleTable;
.fi
.if n \{\
.RE
.\}
.sp
vals
is an array of pointers to rows\&. (The number of valid entries is given by
\fISPI_processed\fR\&.)
tupdesc
is a row descriptor which you can pass to SPI functions dealing with rows\&.
tuptabcxt,
alloced, and
free
are internal fields not intended for use by SPI callers\&.
.PP

\fBSPI_finish\fR
frees all
SPITupleTables allocated during the current procedure\&. You can free a particular result table earlier, if you are done with it, by calling
\fBSPI_freetuptable\fR\&.
.SH "ARGUMENTS"
.PP
const char * \fIcommand\fR
.RS 4
string containing command to execute
.RE
.PP
bool \fIread_only\fR
.RS 4
true
for read\-only execution
.RE
.PP
long \fIcount\fR
.RS 4
maximum number of rows to process or return
.RE
.SH "RETURN VALUE"
.PP
If the execution of the command was successful then one of the following (nonnegative) values will be returned:
.PP
SPI_OK_SELECT
.RS 4
if a
SELECT
(but not
SELECT INTO) was executed
.RE
.PP
SPI_OK_SELINTO
.RS 4
if a
SELECT INTO
was executed
.RE
.PP
SPI_OK_INSERT
.RS 4
if an
INSERT
was executed
.RE
.PP
SPI_OK_DELETE
.RS 4
if a
DELETE
was executed
.RE
.PP
SPI_OK_UPDATE
.RS 4
if an
UPDATE
was executed
.RE
.PP
SPI_OK_INSERT_RETURNING
.RS 4
if an
INSERT RETURNING
was executed
.RE
.PP
SPI_OK_DELETE_RETURNING
.RS 4
if a
DELETE RETURNING
was executed
.RE
.PP
SPI_OK_UPDATE_RETURNING
.RS 4
if an
UPDATE RETURNING
was executed
.RE
.PP
SPI_OK_UTILITY
.RS 4
if a utility command (e\&.g\&.,
CREATE TABLE) was executed
.RE
.PP
SPI_OK_REWRITTEN
.RS 4
if the command was rewritten into another kind of command (e\&.g\&.,
UPDATE
became an
INSERT) by a
rule\&.
.RE
.PP
On error, one of the following negative values is returned:
.PP
SPI_ERROR_ARGUMENT
.RS 4
if
\fIcommand\fR
is
NULL
or
\fIcount\fR
is less than 0
.RE
.PP
SPI_ERROR_COPY
.RS 4
if
COPY TO stdout
or
COPY FROM stdin
was attempted
.RE
.PP
SPI_ERROR_TRANSACTION
.RS 4
if a transaction manipulation command was attempted (BEGIN,
COMMIT,
ROLLBACK,
SAVEPOINT,
PREPARE TRANSACTION,
COMMIT PREPARED,
ROLLBACK PREPARED, or any variant thereof)
.RE
.PP
SPI_ERROR_OPUNKNOWN
.RS 4
if the command type is unknown (shouldn\*(Aqt happen)
.RE
.PP
SPI_ERROR_UNCONNECTED
.RS 4
if called from an unconnected procedure
.RE
.SH "NOTES"
.PP
The functions
\fBSPI_execute\fR,
\fBSPI_exec\fR,
\fBSPI_execute_plan\fR, and
\fBSPI_execp\fR
change both
\fISPI_processed\fR
and
\fISPI_tuptable\fR
(just the pointer, not the contents of the structure)\&. Save these two global variables into local procedure variables if you need to access the result table of
\fBSPI_execute\fR
or a related function across later calls\&.
